#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理模块
"""

import pandas as pd
from sqlalchemy import create_engine, text
from typing import Optional, Dict, Any
import logging
from datetime import datetime, date

from .data_utils_system import calculate_ahs_couponed_price, calculate_zz_couponed_price, calculate_strategy_value, calculate_strategy_quote_price
from .data_utils_system import level_order, level_hierarchy, calculate_inversion_statistics, calculate_big_gap_value, calculate_big_gap_value_human, define_competiitive_starts_ends
from .data_utils_system import modify_tens_price


from ..config.settings import AppConfig
from ..utils.logger import get_logger

logger = get_logger(__name__)

class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, config: AppConfig):
        self.config = config
        self.engine = None
        self._connect()
    
    def _connect(self):
        """建立数据库连接"""
        try:
            self.engine = create_engine(self.config.database.connection_string)
            logger.info("数据库连接成功")
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            raise
    
    def get_engine(self):
        """获取数据库引擎"""
        return self.engine
    
    def execute_query(self, query: str, params: Optional[Dict[str, Any]] = None) -> pd.DataFrame:
        """
        执行SQL查询
        
        Args:
            query: SQL查询语句
            params: 查询参数
        
        Returns:
            查询结果DataFrame
        """
        try:
            if params:
                result = pd.read_sql(query, self.engine, params=params)
            else:
                result = pd.read_sql(query, self.engine)
            
            logger.info(f"查询执行成功，返回 {len(result)} 行数据")
            return result
            
        except Exception as e:
            logger.error(f"查询执行失败: {str(e)}")
            raise
    
    def get_base_data(self, target_date: date, coupon_tag='SELF_1515', custom_coupon_data=None) -> Optional[pd.DataFrame]:
        """  获取指定日期的基础数据 """

        try:
            date_str = target_date.strftime('%Y-%m-%d')
            
            # 检查时间是否满足要求（当天需要15:10之后）
            current_datetime = datetime.now()
            current_date = current_datetime.date()
            current_date_str = str(current_date)
            
            if target_date == current_date and (current_datetime.hour < 10 or (current_datetime.hour == 10 and current_datetime.minute < 20)):
                logger.warning("当天数据需要等到10:20之后才能获取")
                return None
            
            # 查询该日期的全量数据
            query = """
                select 
                    *
                from 
                    rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail  -- 2025-07-21 恢复用亮亮的表

                WHERE
                        partition_date = '{}'
                    and group_id = 219 -- 分城一组
                    and product_category_id = 1
                    and product_brand_name = '苹果'
            """.format(date_str)
            
            base_data = self.execute_query(query)
            
            if base_data.empty:
                logger.warning(f"未找到 {date_str} 日期的数据")
                return None
            
            # 数据预处理
            base_data = self._preprocess_base_data(base_data, coupon_tag, custom_coupon_data)
            
            logger.info(f"成功获取 {date_str} 的基础数据，共 {len(base_data)} 条记录")
            return base_data
            
        except Exception as e:
            logger.error(f"获取基础数据失败: {str(e)}")
            return None
    
    def _preprocess_base_data(self, data: pd.DataFrame, coupon_tag='SELF_1515', custom_coupon_data=None) -> pd.DataFrame:
        """ 预处理基础数据 """

        try:
            # 过滤无效数据
            data = data[data['finalprice'] != '(NULL)']
            data = data[data['product_level_name'] != 'S']
            
            # 处理finalprice字段，确保是数值类型
            data['finalprice'] = data['finalprice'].astype(float)
            
            # 设置人工作价
            data['human_price'] = data['finalprice']
            
            # 填充缺失值
            data['success_nums'] = data['success_nums'].fillna(0).astype(int)
            data['curr_success_nums'] = data['curr_success_nums'].fillna(0).astype(int)
            data['inquiry_nums'] = data['inquiry_nums'].fillna(0).astype(int)

            # 初始化优惠券数据（只调用一次，提高性能）
            from .data_utils_system import init_coupon_data
            init_coupon_data(self.engine, coupon_tag, custom_coupon_data)
            
            # 计算券后价格（直接使用全局coupon_ahs，提高性能）
            data['ahs_couponed_price'] = data['finalprice'].apply(calculate_ahs_couponed_price)
            data['zz_couponed_price_18_added'] = data['zz_pre_coupon_price'].apply(calculate_zz_couponed_price)

            data['参照项'] = data['product_sku_id'].astype(str) + "_" + data['product_level_name'].astype(str)
            data['券后价差-ahs-转转'] = data['ahs_couponed_price'] - data['zz_couponed_price_18_added']
            data['转转追价-策略-中间值'] = data['zz_couponed_price_18_added'].apply(calculate_strategy_value)
            data['转转追价-策略报价'] = data.apply(calculate_strategy_quote_price, axis=1)
            data['转转追价-策略报价-券后'] = data['转转追价-策略报价'].apply(calculate_ahs_couponed_price)

            data['zz_strategy_price'] = data['转转追价-策略报价']
            data['zz_strategy_price_couponed'] = data['转转追价-策略报价-券后']
            
            # 处理标准价格
            data['standard_price'] = data['standard_price'].fillna(data['finalprice'])
            data['standard_price'] = data['standard_price'].replace('(NULL)', 0).astype(int)
            data['standard_price'] = data['standard_price'].astype(int)
            
            # 创建SKU级别标识
            data['sku_level'] = data['product_sku_id'].astype(str) + '_' + data['product_level_name']
            data['sku_A+'] = data['product_sku_id'].astype(str) + '_' + 'A+'
            
            logger.info("基础数据预处理完成")
            return data
            
        except Exception as e:
            logger.error(f"基础数据预处理失败: {str(e)}")
            raise
    
    def get_coupon_data(self, target_date: date) -> Optional[pd.DataFrame]:
        """
        获取优惠券数据
        
        Args:
            target_date: 目标日期
        
        Returns:
            优惠券数据DataFrame
        """
        try:
            date_str = target_date.strftime('%Y-%m-%d')
            
            query = """
            select 
                * 
            from 
                ods.ods_c2babtrader_competitive_price_coupon_detail
            where partition_date = %(date)s
            """
            
            coupon_data = self.execute_query(query, {'date': date_str})
            
            if coupon_data.empty:
                logger.warning(f"未找到 {date_str} 的优惠券数据")
                return None
            
            logger.info(f"成功获取优惠券数据，共 {len(coupon_data)} 条记录")
            return coupon_data
            
        except Exception as e:
            logger.error(f"获取优惠券数据失败: {str(e)}")
            return None
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            test_query = "SELECT 1 as test"
            result = self.execute_query(test_query)
            return len(result) > 0
        except Exception as e:
            logger.error(f"数据库连接测试失败: {str(e)}")
            return False
    
    def get_product_metrics(self, target_date: str = None) -> pd.DataFrame:
        """
        获取产品指标数据
        
        Args:
            target_date: 目标日期，默认为当前日期
            
        Returns:
            包含产品指标数据的DataFrame
        """
        try:
            if target_date is None:
                target_date = datetime.now().strftime('%Y-%m-%d')
            
            metrics_sql = text("""
                SELECT
                    distinct
                    partition_date,  -- 日期
                    product_name,  -- 型号
                    stock_nums, -- 库存量
                    avg_sales_cnt_3days,  -- 近3天日均出库量
                    expected_inventory_turnover,  -- 预计周转天数
                    gross_margin_3_day, -- 近3天毛利率(%)
                    net_profit_margin_3_day,  -- 近3天净利率(%)
                    chaseup_cost_rate_3_day  -- 近3天追价费用率(%)
                FROM rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail
                WHERE product_brand_name = '苹果'
                    AND product_name IN (
                        '苹果 iPhone 12', '苹果 iPhone 13', '苹果 iPhone 14', '苹果 iPhone 15', '苹果 iPhone 16',
                        '苹果 iPhone 12 Pro', '苹果 iPhone 13 Pro', '苹果 iPhone 14 Pro', '苹果 iPhone 15 Pro', '苹果 iPhone 16 Pro',
                        '苹果 iPhone 12 Pro Max', '苹果 iPhone 13 Pro Max', '苹果 iPhone 14 Pro Max', '苹果 iPhone 15 Pro Max', '苹果 iPhone 16 Pro Max'
                    )
                    AND date(partition_date) = date(:target_date)
                ORDER BY product_name
            """)
            
            result = self.execute_query(metrics_sql, {'target_date': target_date})
            
            if result is not None and not result.empty:
                logger.info(f"成功获取产品指标数据: {len(result)} 条记录")
                return result
            else:
                logger.warning("产品指标数据为空")
                return pd.DataFrame()
                
        except Exception as e:
            logger.error(f"获取产品指标数据失败: {str(e)}")
            return pd.DataFrame()